from .services import ExhibitionService
from flask import Blueprint, request, jsonify, current_app,g
import pandas as pd
import re
import os
import jieba
from datetime import datetime
from openpyxl import Workbook
import openpyxl  # 添加这行导入
from openpyxl.styles import PatternFill
from app.config import Config
from app.utils.base import Base
import jieba.posseg as pseg  # 添加到文件开头的导入部分
import uuid
import zipfile
import xml.etree.ElementTree as ET
from io import BytesIO
from PIL import Image as PILImage
from app.src.operation.services import OperationService
from app.database.connector import Database,MySQLHelper
from openpyxl.drawing.image import Image
from openpyxl.styles import Alignment
import requests
from app.celery_task.tasks import ai_completion_exhibition
from celery.result import AsyncResult

db = MySQLHelper()
exhibition_bp = Blueprint('exhibition', __name__)

@exhibition_bp.before_request
def init_services():
    """初始化服务，确保每个请求都有新的数据库连接"""
    g.db = Database()
    g.operation_service = OperationService(db=g.db)
    g.exhibition_service = ExhibitionService(db=g.db)

@exhibition_bp.teardown_request
def close_db(exc):
    """请求结束时关闭数据库连接"""
    db = g.pop('db', None)
    if db is not None:
        db.close()

@exhibition_bp.before_request
def check_auth():
    """验证用户认证并设置g.user"""
    from app.src.auth.services import AuthService
    
    # 跳过 OPTIONS 请求（常见于CORS预检）
    if request.method == 'OPTIONS':
        return
    
    token = request.headers.get('Authorization')
    if not token:
        current_app.logger.warning("请求未提供Authorization头")
        # 不要直接返回401，而是记录状态并继续
        g.user = {'username': 'anonymous'}
        return
    
    # 移除Bearer前缀
    if token.startswith('Bearer '):
        token = token[7:]
    
    try:
        auth_service = AuthService()
        result = auth_service.verify_token(token)
        
        if result.get('valid'):
            g.user = result.get('user_info')
            current_app.logger.info(f"用户认证成功: {g.user.get('username')}")
        else:
            current_app.logger.warning(f"token验证失败: {result.get('message')}")
            g.user = {'username': 'unknown'}
    except Exception as e:
        current_app.logger.error(f"认证过程中出错: {str(e)}")
        g.user = {'username': 'error'}

# 获取展会列表
@exhibition_bp.route('/list', methods=['GET'])
def get_exhibition_list():
    """获取展会列表"""
    page = int(request.args.get('page', 1))
    page_size = int(request.args.get('pageSize', 10))
    order_by = request.args.get('order_by', 'id')  # 添加排序参数，默认倒序
    order = request.args.get('order', 'desc')  # 添加排序参数，默认倒序
    filters = {
        'chinese_name': request.args.get('chinese_name', '').strip(),
        'exhibition_number': request.args.get('exhibition_number', '').strip(),
        'continent': request.args.get('continent', '').strip(),
        'country': request.args.get('country', '').strip(),
        'city': request.args.get('city', '').strip(),
        'industry': request.args.get('industry', '').strip(),
        'is_supply_chain': request.args.get('is_supply_chain', '').strip(),
        'is_ccpit': request.args.get('is_ccpit', '').strip(),
        # 添加日期范围过滤
        'date_from': request.args.get('date_from', '').strip(),
        'date_to': request.args.get('date_to', '').strip(),
        # 移除行业与AI不一致的过滤，因为数据库中不存在该字段
        # 'is_industry_mismatch': request.args.get('is_industry_mismatch', '').strip()
    }
    
    # 获取is_industry_mismatch参数但不添加到filters，我们将在服务层处理它
    is_industry_mismatch = request.args.get('is_industry_mismatch', '').strip()
    
    # 检查数据库中是否存在industry_ai字段
    try:
        result, total = g.exhibition_service.get_exhibition_list(page, page_size, filters, order_by, order, is_industry_mismatch)
    except Exception as e:
        current_app.logger.error(f"获取展会列表失败: {str(e)}")
        # 如果发生错误，尝试不使用is_industry_mismatch过滤条件重新获取
        result, total = g.exhibition_service.get_exhibition_list(page, page_size, filters, order_by, order)

    return jsonify({
        'items': result,
        'total': total,
        'page': page,
        'pageSize': page_size
    })

@exhibition_bp.route('/detail', methods=['GET'])
def get_exhibition():
    """获取展会详情"""
    exhibition_id = request.args.get('id')
    if not exhibition_id:
        return jsonify({'error': '展会ID不能为空'}), 400
        
    exhibition = g.exhibition_service.get_exhibition_by_id(exhibition_id)
    if not exhibition:
        return jsonify({'error': '展会不存在'}), 404
    return jsonify(exhibition)


# 请你在写一个函数 触发 任务 web_url 访问
@exhibition_bp.route('/visit_web', methods=['GET'])
def visit_web():
    """触发任务 web_url 访问"""
    data = request.get_json()
    web_url = data.get('web_url')
    web_visit_counts = data.get('web_visit_counts')
    web_area = data.get('web_area') 
    
    # 安全获取用户名
    username = 'unknown'
    if hasattr(g, 'user') and g.user:
        username = g.user.get('username', 'unknown')
    
    task = visit_web.delay(username, web_url, web_visit_counts, web_area)
    return jsonify({
        'status': True,
        'message': '任务已提交',
        'task_id': task.id
    })

#  现在我需要新增一个 对 展会名称进行修改
#  就是 匹配 这个展会名称是否 有这个国家名字  如果没有就给加上就行

@exhibition_bp.route('/create', methods=['POST'])
def create_exhibition():
    """保存展会（创建或更新）"""
    data = request.get_json()
    # 对数据进行 trim 处理
    cleaned_data = {}
    for key, value in data.items():
        if isinstance(value, str):
            cleaned_data[key] = value.strip()
        else:
            cleaned_data[key] = value
    print(data, "测试是数据是否传过来了?")
    
    # 安全地获取用户名，避免AttributeError
    username = 'unknown'
    try:
        # 尝试通过多种方式获取用户名
        if hasattr(g, 'user') and g.user:
            username = g.user.get('username', 'unknown')
        
        # 获取认证头信息并记录（仅用于调试）
        auth_header = request.headers.get('Authorization', 'None')
        current_app.logger.info(f"Auth header: {auth_header}")
    except Exception as e:
        current_app.logger.error(f"获取用户信息失败: {str(e)}")
    
    # 设置用户名
    cleaned_data['username'] = username

    # 准备AI补全任务的数据参数
    task_data = {
        'username': username,
        'chinese_name': cleaned_data.get('chinese_name'),
        'official_website': cleaned_data.get('official_website'),
        # 其他可能需要的字段
        'exhibition_number': cleaned_data.get('exhibition_number')
    }

    # 调用AI补全任务，传递单个字典参数
    task = ai_completion_exhibition.delay(task_data)

    return jsonify({
        'status': True,
        'message': 'AI补全任务已提交',
        'task_id': task.id,
        'monitor_url': f'/exhibition/task_status?task_id={task.id}'
    })


@exhibition_bp.route('/save', methods=['POST'])
def save_exhibition():
    """保存展会（创建或更新）"""
    try:
        data = request.get_json()
        exhibition_id = data.get('id')

        # 需要过滤的AI相关字段
        ai_fields = {
            'advice_check_description',
            'ai_asstance_link',
            'ai_checked_status',
            'ai_checked_time',
            'ai_link',
            'ai_link_status',
            'ai_link_time'
        }
        # 对数据进行 trim 处理
        cleaned_data = {}
        for key, value in data.items():
            if key not in ai_fields:  # 跳过AI相关字段
                if isinstance(value, str):
                    cleaned_data[key] = value.strip()
                else:
                    cleaned_data[key] = value

        # 常规保存逻辑
        if exhibition_id:
            # 更新展会
            if cleaned_data.get('holding_time'):
                match = re.match(r'^(\d+)月(\d+)日-(\d+)月(\d+)日$', str(cleaned_data['holding_time']))
                if match:
                    start_month, start_day, end_month, end_day = map(int, match.groups())
                    current_year = datetime.now().year
                    
                    cleaned_data['event_date_start'] = datetime(current_year, start_month, start_day).strftime('%Y-%m-%d %H:%M:%S')
                    cleaned_data['event_date_end'] = datetime(current_year, end_month, end_day).strftime('%Y-%m-%d %H:%M:%S')
                    cleaned_data['event_month'] = f"{start_month}"
            result = g.exhibition_service.update_exhibition(exhibition_id, cleaned_data)
            return jsonify({'code': 200,'message': '更新成功'})
    except Exception as e:
        current_app.logger.error(f"保存展会信息失败: {str(e)}")
        return jsonify({
            'code': 400,
            'error': f'保存展会信息失败: {str(e)}'
        }), 200

@exhibition_bp.route('/delete/<int:exhibition_id>', methods=['DELETE'])
def delete_exhibition(exhibition_id):
    """删除展会"""
    if not exhibition_id:
        return jsonify({'error': '展会ID不能为空'}), 400
        
    g.exhibition_service.delete_exhibition(exhibition_id)
    return jsonify({'message': '删除成功'})

@exhibition_bp.route('/import', methods=['POST'])
def import_exhibitions():
    """导入展会数据"""
    
    # 安全获取用户名
    username = 'unknown'
    if hasattr(g, 'user') and g.user:
        username = g.user.get('username', 'unknown')
    
    if 'file' not in request.files:
        # 导入失败的日志记录（文件未上传）
        g.operation_service.create_log({
            'username': username,
            'operation_type': 'import_excel',
            'filename': '',
            'status': 0,
            'report_url': '',
            'description': '未上传文件'
        })
    
    file = request.files['file']
    if file.filename == '':
        g.operation_service.create_log({
            'username': username,
            'operation_type': 'import_excel',
            'filename': '',
            'status': 0,
            'report_url': '',
            'description': '未选择文件'
        })
        return jsonify({'error': '未选择文件'}), 400

    try:
        
        # 保存上传的文件到临时位置
        temp_file_path = os.path.join(current_app.static_folder, 'temp', file.filename)
        os.makedirs(os.path.dirname(temp_file_path), exist_ok=True)
        file.save(temp_file_path)

        excel_file = None
        try:
            

            excel_file = pd.ExcelFile(temp_file_path)
            imported_count = 0
            skipped_count = 0
            duplicate_records = []
            

            
            # 检查文件内容
            check_result = check_exhibitions()
            check_data = check_result.get_json()
            
            if not check_data.get('success'):
                if check_data.get('required_errors') > 0:
                    return check_result
            # 提取嵌入图片的映射关系
            image_urls = extract_embed_images(temp_file_path)

            g.db.begin_transaction()

            for sheet_name in excel_file.sheet_names:
                if sheet_name == "WpsReserved_CellImgList":
                    continue
                
                
                # 使用 with 语句处理 openpyxl 工作簿
                
                wb = openpyxl.load_workbook(temp_file_path)
                sheet = wb[sheet_name]
                
                # 获取标识列的索引
                headers = [cell.value for cell in sheet[1]]
                logo_col_idx = -1
                for idx, header in enumerate(headers):
                    if header and isinstance(header, str) and header == '标识':
                        logo_col_idx = idx
                        break
                    
                df = pd.read_excel(excel_file, sheet_name=sheet_name)
                
                # 字段映射
                field_mapping = {
                    '展览会编号': 'exhibition_number',
                    '中文名称': 'chinese_name',
                    '英文全称': 'english_name',
                    '英文简称': 'english_short_name',
                    '标识': 'logo',
                    '举办时间': 'holding_time',
                    '备注2洲': 'continent',
                    '国别': 'country',
                    '城市': 'city',
                    '举办频率': 'frequency',
                    '行业': 'industry',
                    '主办方': 'organizer',
                    '主办方英文': 'organizer_en',
                    '联系方式': 'contact_info',
                    '展馆': 'exhibition_hall',
                    '展馆英文': 'exhibition_hall_english',
                    '官网': 'official_website',
                    '展品范围': 'exhibit_scope',
                    '简介': 'description',
                    '是否UFI认证': 'ufi_certified',
                    '备注': 'remarks',
                    '备注1': 'remarkx',
                    '展会面积（㎡）': 'exhibition_area'
                }
                
                # 重命名列名
                df = df.rename(columns=field_mapping)
                # 开始事务
                
                
                # 遍历每一行数据
                # 跳过表头，从第二行开始读取
                
                for idx, row in df.iloc[0:].iterrows():
                    # 处理数据，将 nan 转换为空字符串，数值字段转换为 0
                    exhibition_data = {
                        k: (0 if k == 'exhibition_area' and pd.isna(v) else 
                            '' if pd.isna(v) else v) 
                        for k, v in row.to_dict().items()
                    }
                    
                    # 处理logo图片
                    if logo_col_idx >= 0:
                        cell = sheet.cell(row=idx+2, column=logo_col_idx+1)
                        if cell is not None:
                            # 确保images目录存在
                            images_dir = os.path.join(current_app.static_folder, 'images', 'logos')
                            os.makedirs(images_dir, exist_ok=True)
                            
                            image_path = extract_image_from_excel(cell, image_urls, temp_file_path)
                            if image_path:
                                # 获取文件名并构建相对URL路径
                                image_filename = os.path.basename(image_path)
                                image_url = f'/static/images/logos/{image_filename}'
                                exhibition_data['logo'] = image_url
                    
                    # 处理 UFI 认证状态
                    ufi_map = {'是': 1, '否': 2}
                    exhibition_data['ufi_certified'] = ufi_map.get(str(exhibition_data.get('ufi_certified')), 0)
                    
                    # 处理日期时间
                    if exhibition_data.get('holding_time'):
                        match = re.match(r'^(\d+)月(\d+)日-(\d+)月(\d+)日$', str(exhibition_data['holding_time']))
                        if match:
                            start_month, start_day, end_month, end_day = map(int, match.groups())
                            current_year = datetime.now().year
                            
                            exhibition_data['event_date_start'] = datetime(current_year, start_month, start_day).strftime('%Y-%m-%d %H:%M:%S')
                            exhibition_data['event_date_end'] = datetime(current_year, end_month, end_day).strftime('%Y-%m-%d %H:%M:%S')
                            exhibition_data['event_month'] = f"{start_month}"
                    
                    # 添加状态和时间戳
                    exhibition_data['state'] = 0
                    exhibition_data['created_at'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                    exhibition_data['updated_at'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                    
                    # 移除 None 值和不存在的字段
                    exhibition_data = {k: v for k, v in exhibition_data.items() 
                                    if k in field_mapping.values() or k in ['state', 'created_at', 'updated_at', 'event_date_start', 'event_date_end', 'event_month']
                                    and pd.notna(v)}
                    
                    # 检查展览会编号重复
                    if exhibition_data.get('exhibition_number') and str(exhibition_data['exhibition_number']).isdigit():
                        exhibition_data['exhibition_number'] = int(str(exhibition_data['exhibition_number']).strip())
                        existing_exhibition = g.exhibition_service.get_exhibition_by_code(str(exhibition_data['exhibition_number']))
                        if existing_exhibition:
                            duplicate_records.append({
                                'sheet': sheet_name,
                                'row': idx + 2,
                                'reason': f"展览会编号 {exhibition_data['exhibition_number']} 已存在"
                            })
                            skipped_count += 1
                            continue
                    
                    # 检查中文名称重复
                    if exhibition_data.get('chinese_name') and exhibition_data.get('holding_time'):
                        existing_name = g.exhibition_service.get_exhibition_by_name_and_time(
                            exhibition_data['chinese_name'],
                            exhibition_data['holding_time']
                        )
                        if existing_name:
                            duplicate_records.append({
                                'sheet': sheet_name,
                                'row': idx + 2,
                                'reason': f"展会名称 {exhibition_data['chinese_name']} 在相同举办时间 {exhibition_data['holding_time']} 已存在"
                            })
                            skipped_count += 1
                            continue
                    # 添加文件来源信息
                    exhibition_data['source'] = file.filename
                    exhibition_data['source_user'] = username
                    # 保存到数据库
                    res = g.exhibition_service.create_exhibition(exhibition_data)
                    if res is None:
                        duplicate_records.append({
                            'sheet': sheet_name,
                            'row': idx + 2,
                            'reason': f"展会名称 {exhibition_data['chinese_name']} 插入失败"
                        })
                        skipped_count += 1
                        continue
                    imported_count += 1
                    duplicate_records.append({
                            'sheet': sheet_name,
                            'row': idx + 2,
                            'reason': f"展会名称 {exhibition_data['chinese_name']} 插入成功"
                        })
                    

            g.db.commit()
            # 如果有重复记录，生成Excel报告
            if duplicate_records:
                wb = Workbook()
                ws = wb.active
                ws.title = "重复数据报告"
                
                # 写入表头
                headers = ['Sheet名称', '行号', '重复原因']
                for col, header in enumerate(headers, 1):
                    ws.cell(row=1, column=col, value=header)
                    ws.column_dimensions[chr(64 + col)].width = 20
                
                # 写入数据
                for row, record in enumerate(duplicate_records, 2):
                    ws.cell(row=row, column=1, value=record['sheet'])
                    ws.cell(row=row, column=2, value=record['row'])
                    ws.cell(row=row, column=3, value=record['reason'])
                    
            # 保存报告
            report_path = os.path.join(current_app.static_folder, 'reports')
            os.makedirs(report_path, exist_ok=True)
            report_file = os.path.join(report_path, f'import_report_{datetime.now().strftime("%Y%m%d%H%M%S")}.xlsx')
            wb.save(report_file)
                 
            # 记录带有重复数据的导入日志
            g.operation_service.create_log({
                'username': username,
                'operation_type': 'import_excel',
                'filename': file.filename,
                'status': 1,  # 操作成功
                'report_url': f'/static/reports/{os.path.basename(report_file)}',
                'description': f'成功导入 {imported_count} 条数据，跳过 {skipped_count} 条重复数据'
            })
            
            return jsonify({
                'success': True,
                'message': f'成功导入 {imported_count} 条数据，跳过 {skipped_count} 条重复数据',
                'imported_count': imported_count,
                'skipped_count': skipped_count,
                'reportUrl': f'{Config.API_URL}/static/reports/{os.path.basename(report_file)}'
            })
                

        finally:
            
            # 确保 Excel 文件被关闭
            if excel_file is not None:
                excel_file.close()
            
            # 等待一小段时间确保文件句柄完全释放
            import time
            time.sleep(0.5)
            
            # 尝试删除临时文件
            try:
                if os.path.exists(temp_file_path):
                    os.remove(temp_file_path)
            except Exception as del_e:
                current_app.logger.warning(f"无法删除临时文件 {temp_file_path}: {str(del_e)}")
                # 如果第一次删除失败，再等待一段时间后重试
                time.sleep(1)
                try:
                    if os.path.exists(temp_file_path):
                        os.remove(temp_file_path)
                except Exception as del_e2:
                    current_app.logger.error(f"第二次尝试删除临时文件失败 {temp_file_path}: {str(del_e2)}")            
    except Exception as e:
        g.db.rollback()
        current_app.logger.error(f"导入Excel文件失败: {str(e)}")
        # 确保清理临时文件
        try:
            if 'temp_file_path' in locals() and os.path.exists(temp_file_path):
                if excel_file is not None:
                    excel_file.close()
                time.sleep(0.5)
                os.remove(temp_file_path)
        except Exception as del_e:
            current_app.logger.warning(f"无法删除临时文件 {temp_file_path}: {str(del_e)}")
        return jsonify({'error': '文件导入失败'}), 500


@exhibition_bp.route('/checkexcel', methods=['POST'])
def check_exhibitions():
    """检查展会数据"""
    # 安全获取用户名
    username = 'unknown'
    if hasattr(g, 'user') and g.user:
        username = g.user.get('username', 'unknown')
        
    if 'file' not in request.files:
        return jsonify({'error': '未上传文件'}), 400

    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': '未选择文件'}), 400

    try:
        # 读取Excel文件的所有sheet
        excel_file = pd.ExcelFile(file)
        all_sheets_errors = []


        

        # 定义必填字段
        required_fields = [
            '展览会编号', '中文名称', '英文全称', '英文简称', '标识', 
            '举办时间', '备注2洲', '国别', '城市', '举办频率', '行业',
            '主办方', '主办方英文', '联系方式', '展馆', '展馆英文',
            '官网', '展品范围', '简介', '是否UFI认证', '备注', '展会面积（㎡）'
        ]

        for sheet_name in excel_file.sheet_names:
            if sheet_name == "WpsReserved_CellImgList":
                continue
            df = pd.read_excel(excel_file, sheet_name=sheet_name)
            sheet_errors = []


            # 检查必填字段是否存在
            missing_fields = [field for field in required_fields if field not in df.columns]
            if missing_fields:
                sheet_errors.append({
                    'Sheet': sheet_name,
                    '行号': 1,
                    '缺少字段': (f'缺少必填字段：{", ".join(missing_fields)}', True)
                })
                all_sheets_errors.extend(sheet_errors)
                continue  # 如果缺少必填字段，跳过该sheet的其他检查
        if not all_sheets_errors:
            
            for sheet_name in excel_file.sheet_names:
                if sheet_name == "WpsReserved_CellImgList":
                    continue
                df = pd.read_excel(excel_file, sheet_name=sheet_name)
                sheet_errors = []


                # 检查必填字段是否存在
                missing_fields = [field for field in required_fields if field not in df.columns]
                if missing_fields:
                    sheet_errors.append({
                        'Sheet': sheet_name,
                        '行号': 1,
                        '展览会编号': (f'缺少必填字段：{", ".join(missing_fields)}', True)
                    })
                    all_sheets_errors.extend(sheet_errors)
                    continue  # 如果缺少必填字段，跳过该sheet的其他检查

                # 定义检查规则
                rules = {
                    '展览会编号': lambda x: ("展览会编号必须是2-6位数字", True) if pd.notna(x) and str(x).isdigit() and not bool(
                        re.match('^\d{2,6}$',
                                                                                                                    str(int(x)))) else True,
                    '中文名称': lambda x: ("中文名称不能为空", True) if not pd.notna(x) else check_chinese_name(x, row.get('国别', ''), row.get('城市', '')),
                    '国别': lambda x: ("国别不能为空", True) if not pd.notna(x) else check_location_word(x, 'ns'),
                    '城市': lambda x: ("城市不能为空", True) if not pd.notna(x) else check_location_word(x, 'ns'),
                    '英文全称': lambda x: ("英文全称只能包含大写字母、数字、点、空格、连字符、括号、逗号、冒号和&符号", False) if pd.notna(x) and not bool(re.match(
                        '^['
                                                                                                                                            '0-9A-ZÄÖÜäöüßéèêëàâôûùÿçáíóúñÁÍÓÚÑœæÀÂÆŒÈÉÊËÎÏÔŒÙÛÜŸãõâêôîûáéíóúàèìòùÃÕÂÊÔÎÛÁÉÍÓÚÀÈÌÒÙАБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯабвгдеёжзийклмнопрстуфхцчшщъыьэюя. +\-\'()（）&,:]+$', str(x))) else True,
                    '英文简称': lambda x: ("英文简称只能包含大写字母、数字、点、空格和连字符", False) if pd.notna(x) and not bool(re.match('^[0-9A-Z. '
                                                                                                                                    '+\-]+$', str(x))) else True,
                    '举办时间': lambda x: ("举办时间格式必须为：数字月数字日-数字月数字日", False) if pd.notna(x) and not bool(re.match('^[0-9]+月[0-9]+日-[0-9]+月[0-9]+日$', str(x))) else True,
                    '备注2洲': lambda x: ("洲必须是：亚洲、欧洲、非洲、北美洲、南美洲或大洋洲", True) if pd.notna(x) and str(x) not in ['亚洲', '欧洲', '非洲', '北美洲', '南美洲', '大洋洲'] else True,
                    '举办频率': lambda x: ("举办频率必须是：一年一届、一年两届、一年四届、两年一届、三年一届或四年一届", True) if pd.notna(x) and str(x) not in [
                        '一年一届', '一年两届', '一年四届', '两年一届', '三年一届', '四年一届'] else True,
                    '行业': lambda x: ("行业类型不在允许范围内", True) if pd.notna(x) and str(x) not in Base.INDUSTRY_TYPES else True,
                    '主办方': lambda x: ("主办方名称只能包含数字、汉字、大小写字母、德语字母和顿号,连字符，括号", True) if pd.notna(x) and not bool(re.match('^[\u4e00-\u9fa5A-Za-z0-9ÄÖÜäöüßéèêëàâôûùÿçáíóúñÁÍÓÚÑœæÀÂÆŒÈÉÊËÎÏÔŒÙÛÜŸãõâêôîûáéíóúàèìòùÃÕÂÊÔÎÛÁÉÍÓÚÀÈÌÒÙАБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯабвгдеёжзийклмнопрстуфхцчшщъыьэюя&.、 （）()-]+$', str(x))) else True,
                    '主办方英文': lambda x: ("主办方英文名称格式不正确", True) if pd.notna(x) and not bool(re.match('^[0-9a-zA-Z&.,/ （）()ÄÖÜäöüßéèêëàâôûùÿçáíóúñÁÍÓÚÑœæÀÂÆŒÈÉÊËÎÏÔŒÙÛÜŸãõâêôîûáéíóúàèìòùÃÕÂÊÔÎÛÁÉÍÓÚÀÈÌÒÙАБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯабвгдеёжзийклмнопрстуфхцчшщъыьэюя\-\']+$', str(x))) else True,
                    '联系方式': lambda x: check_contact_format(x) if pd.notna(x) else True,
                    '展馆': lambda x: ("展馆不能为空", False) if not pd.notna(x) else check_venue_name(x, row.get('城市', '')),
                    '展馆英文': lambda x: ("展馆英文名称格式不正确", False) if pd.notna(x) and not bool(re.match('^[A-Za-z\-\'ÄÖÜäöüßéèêëàâôûùÿçáíóúñÁÍÓÚÑœæÀÂÆŒÈÉÊËÎÏÔŒÙÛÜŸãõâêôîûáéíóúàèìòùÃÕÂÊÔÎÛÁÉÍÓÚÀÈÌÒÙАБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯабвгдеёжзийклмнопрстуфхцчшщъыьэюя&., +]+$', str(x))) else True,
                    '官网': lambda x: ("官网格式不正确", True) if pd.notna(x) and not bool(re.match('^(?:www\.)?[a-z0-9-]+(?:\.[a-z0-9-]+)*(?:\.[a-z]+|\.org\.[a-z]+)(?:/[a-zA-Z0-9-_/.]*)?(?:\?[a-zA-Z0-9=&_]*)?$', str(x))) else True,
                    '展品范围': lambda x: ("展品范围不能为空", True) if not pd.notna(x) else check_product_scope(x),
                    '简介': lambda x: ("简介不能为空", False) if not pd.notna(x) else check_description(x),
                    '是否UFI认证': lambda x: ("UFI认证必须填写'是否'或空", True) if pd.notna(x) and str(x) not in ['是','否'] else True,
                    '展会面积（㎡）': lambda x: ("展会面积必须是纯数字", False) if pd.notna(x) and not str(x).isdigit() else True
                }

                # 检查每一行数据
                for idx, row in df.iterrows():
                    row_errors = {}
                    
                    # 检查每个字段
                    for field, rule in rules.items():

                        if field in row:
                            result = rule(row[field])
                            if isinstance(result, tuple):  # 如果返回的是元组，说明是错误信息
                                error_msg, is_required = result
                                row_errors[field] = ("【"+ error_msg + "】" + f'{row[field]}',is_required)


                    # 特殊检查：英文简称不能和全称相同
                    if pd.notna(row.get('英文简称')) and pd.notna(row.get('英文全称')) and row['英文简称'] == row['英文全称']:
                        row_errors['英文简称'] = (f"英文简称不能与全称相同:{row['英文简称']}/{row['英文全称']}", True)


                    if row_errors:
                        sheet_errors.append({'Sheet': sheet_name, '行号': idx+2, **row_errors})
                
                all_sheets_errors.extend(sheet_errors)


            # 第一次遍历：收集所有展馆名称
            for sheet_name in excel_file.sheet_names:
            # 创建字典存储展馆英文名称和中文名称的对应关系

            
                if sheet_name == "WpsReserved_CellImgList":
                    continue
                df = pd.read_excel(excel_file, sheet_name=sheet_name)
                
                venue_names = {}  # {英文名称: {中文名称, sheet名, 行号}}
                # 处理数据，将 nan 转换为空字符串，数值字段转换为 0
                for idx, row in df.iterrows():
                    
                    # 使用处理后的数据继续后续操作
                    if pd.notna(row.get('展馆英文')) and pd.notna(row.get('展馆')):
                        eng_name = str(row['展馆英文']).strip()
                        cn_name = str(row['展馆']).strip()
                        
                        if eng_name not in venue_names:
                            venue_names[eng_name] = {'names': set(), 'locations': []}
                        
                        venue_names[eng_name]['names'].add(cn_name)
                        venue_names[eng_name]['locations'].append({
                            'sheet': sheet_name,
                            'row': idx + 2,
                            'name': cn_name
                        })

                # 添加展馆名称一致性检查的错误
                for eng_name, info in venue_names.items():
                    if len(info['names']) > 1:  # 如果同一个英文名称对应多个中文名称
                        for location in info['locations']:
                            all_sheets_errors.append({
                                'Sheet': location['sheet'],
                                '行号': location['row'],
                                '展馆': (f'展馆英文名称 "{eng_name}" 对应的中文名称不一致，当前为 "{location["name"]}"', True)
                            })
        # 生成错误报告Excel
        
        
        if all_sheets_errors:
            wb = Workbook()
            
            # 按sheet名称对错误进行分组
            errors_by_sheet = {}
            for error in all_sheets_errors:
                sheet_name = error['Sheet']
                if sheet_name not in errors_by_sheet:
                    errors_by_sheet[sheet_name] = []
                errors_by_sheet[sheet_name].append(error)

            
            # 为每个sheet创建对应的错误报告sheet
            for sheet_name, sheet_errors in errors_by_sheet.items():
                ws = wb.create_sheet(title=f"{sheet_name}-检查报告")
                

                # 检查是否有缺少字段的错误
                missing_fields_error = next((error for error in sheet_errors if '缺少字段' in error), None)
                
                
                if missing_fields_error:
                    # 如果有缺少字段错误，只显示这个错误
                    headers = ['行号', '缺少字段']  # 修改为正确的列名
                    
                    # 写入表头并设置列宽
                    for col, header in enumerate(headers, 1):
                        cell = ws.cell(row=1, column=col, value=header)
                        column_width = max(15, min(50, int(len(str(header)) * 2.5)))
                        ws.column_dimensions[chr(64 + col)].width = column_width
                    
                    # 冻结首行
                    ws.freeze_panes = 'A2'
                    
                    # 写入错误数据
                    ws.cell(row=2, column=1, value=missing_fields_error['行号'])
                    error_msg = missing_fields_error.get('缺少字段')  # 直接获取缺少字段的错误信息
                    if isinstance(error_msg, tuple):
                        msg, is_required = error_msg
                        cell = ws.cell(row=2, column=2, value=msg)
                        cell.alignment = cell.alignment.copy(wrap_text=True)
                        cell.fill = PatternFill(start_color='FFB6C1', end_color='FFB6C1', fill_type='solid')
                    ws.row_dimensions[2].height = 30
                    continue  # 跳过其他错误的处理
                else:
                    # 写入表头，保持字段顺序
                    headers = ['行号']
                    for field in required_fields:
                        if any(field in error for error in sheet_errors):
                            headers.append(field)
                    
                    # 写入表头并设置列宽
                    for col, header in enumerate(headers, 1):
                        cell = ws.cell(row=1, column=col, value=header)
                        column_width = max(15, min(50, int(len(str(header)) * 2.5)))
                        ws.column_dimensions[chr(64 + col)].width = column_width
                    
                    # 冻结首行
                    ws.freeze_panes = 'A2'
                    
                    # 写入错误数据
                    for row, error in enumerate(sheet_errors, 2):
                        ws.cell(row=row, column=1, value=error['行号'])
                        for col, header in enumerate(headers[1:], 2):
                            if header in error:
                                error_msg = error[header]
                                if isinstance(error_msg, tuple):
                                    msg, is_required = error_msg
                                    cell = ws.cell(row=row, column=col, value=msg)
                                    cell.alignment = cell.alignment.copy(wrap_text=True)
                                    # 根据是否必填设置不同的背景色
                                    if is_required:
                                        cell.fill = PatternFill(start_color='FFB6C1', end_color='FFB6C1', fill_type='solid')  # 浅红色
                                    else:
                                        cell.fill = PatternFill(start_color='FFFFE0', end_color='FFFFE0', fill_type='solid')  # 浅黄色
                                else:
                                    cell = ws.cell(row=row, column=col, value=error_msg)
                                    cell.alignment = cell.alignment.copy(wrap_text=True)
                                ws.row_dimensions[row].height = 30

                    # 设置列宽
                    for col, header in enumerate(headers, 1):
                        column_width = max(15, min(50, int(len(str(header)) * 2.5)))
                        ws.column_dimensions[chr(64 + col)].width = column_width

            # 删除默认创建的sheet
            if 'Sheet' in wb.sheetnames:
                wb.remove(wb['Sheet'])

            # 保存错误报告
            report_path = os.path.join(current_app.static_folder, 'reports')
            os.makedirs(report_path, exist_ok=True)
            report_file = os.path.join(report_path, f'check_report_{datetime.now().strftime("%Y%m%d%H%M%S")}.xlsx')
            wb.save(report_file)

            # 统计必填错误的数量
            required_errors_count = sum(
                1 for error in all_sheets_errors
                for field, value in error.items()
                if isinstance(value, tuple) and value[1]  # value[1] 是 is_required
            )

            # 检查通过时记录成功日志
            
            g.operation_service.create_log({
                'username': username,
                'operation_type': 'check_excel',
                'filename': file.filename,
                'status': 0,  # 操作成功
                'report_url': f'/static/reports/{os.path.basename(report_file)}',
                'description': f'发现{len(all_sheets_errors)}个错误，其中{required_errors_count}个必改错误'
            })

            return jsonify({
                'success': False,
                'message': f'发现{len(all_sheets_errors)}个错误，其中{required_errors_count}个必改错误',
                'total': len(all_sheets_errors),
                'required_errors': required_errors_count,
                'reportUrl': f'{Config.API_URL}/static/reports/{os.path.basename(report_file)}'
            })

        # 检查通过时记录成功日志
        
        g.operation_service.create_log({
            'username': username,
            'operation_type': 'check_excel',
            'filename': file.filename,
            'status': 1,  # 操作成功
            'report_url': '',
            'description': '检查通过，未发现错误'
        })
        return jsonify({
            'success': True,
            'message': '检查通过，未发现错误'
        })

    except Exception as e:
        # 异常情况下的日志记录
        g.operation_service = OperationService()
        g.operation_service.create_log({
            'username': username,
            'operation_type': 'check_excel',
            'filename': file.filename if 'file' in locals() else 'unknown',
            'status': 0,  # 操作失败
            'report_url': '',
            'description': f'检查Excel文件失败: {str(e)}'
        })
        current_app.logger.error(f"检查Excel文件失败: {str(e)}")
        return jsonify({'error': '文件检查失败'}), 500


# 添加中文名称检查函数
def check_chinese_name(name, country, city):
    if not pd.notna(name):
        return ("展会名称不能为空", True)
    
    if not bool(re.match('^[\u4e00-\u9fa5A-Z0-9()（）\-、]+$', str(name))):
        return ("展会名称只能包含汉字、英文大写字母和顿号，括号，横线", True)
    
    words = list(pseg.cut(name))
    
    if not pd.notna(country):
        return ("国别不能为空", True)
    if not name.startswith(country):
        return (f"展会名称建议以国别（{country}）开头", False)
    
    if len(words) > 1 and words[1].flag == 'ns':
        if pd.notna(city) and not name.startswith(city, len(country)):
            return (f"展会名称中的城市建议与城市字段（{city}）一致", False)
    
    return True

# 添加地名检查函数
def check_location_word(word, expected_flag):
    if not pd.notna(word):
        return ("地名不能为空", True)
    return True

# 添加联系方式格式检查函数
def check_contact_format(contact):
    if not pd.notna(contact):
        return ("联系方式不能为空", False)
    
    phone_pattern = r'^\+\d+ \(\d+\) \d{1,4}-\d{4,8}$'
    email_pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
    
    if not (bool(re.match(phone_pattern, str(contact))) or bool(re.match(email_pattern, str(contact)))):
        return ("联系方式格式错误，需要是电话号码(+xx (xx) xxx-xxxx)或邮箱地址(xxx@xxx.xxx)", False)
    return True

# 添加展馆名称检查函数
def check_venue_name(venue, city):
    if not pd.notna(venue):
        return ("展馆名称不能为空", True)
    if not pd.notna(city):
        return ("城市不能为空", True)
    
    words = list(pseg.cut(str(venue)))
    if not words:
        return ("展馆名称解析失败", True)
    
    if not venue.startswith(city):
        return (f"展馆名称建议以城市名（{city}）开头", False)
    return True

# 添加展品范围检查函数
def check_product_scope(text):
    if not pd.notna(text):
        return ("展品范围不能为空", True)
    
    text = str(text).strip()
    
    if not text:
        return ("展品范围不能为空字符串", True)
    
    if len(text) > 100:
        return ("展品范围不能超过100字", False)
    
    if text.endswith('。'):
        return ("展品范围不能以句号结尾", True)
    
    if text != text.strip():
        return ("展品范围不能有前后空格", False)
    
    return True

# 添加简介检查函数
def check_description(text):
    if not pd.notna(text):
        return ("简介不能为空", False)
    
    text = str(text).strip()
    
    if not text:
        return ("简介不能为空字符串", False)
    
    if len(text) > 150:
        return ("简介不能超过150字", False)
    
    if text.endswith('。'):
        return ("简介不能以句号结尾", False)
    
    if text != text.strip():
        return ("简介不能有前后空格", False)
    
    return True


def extract_embed_images(path):
    """
    提取Excel表中的嵌入图片并上传
    :param path: 文件指针
    :return: 图片URL字典
    """
    image_urls = {}
    embed_map = {}
    id_target_map = {}
    with zipfile.ZipFile(path, 'r') as zfile:
        with zfile.open("xl/cellimages.xml") as file:
            xml_content = file.read()
        with zfile.open("xl/_rels/cellimages.xml.rels") as file:
            relxml_content = file.read()
        root = ET.fromstring(xml_content)
        namespaces = {
            'xdr': 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing',
            'a': 'http://schemas.openxmlformats.org/drawingml/2006/main'
        }
        for pic in root.findall('.//xdr:pic', namespaces=namespaces):
            name = pic.find('.//xdr:cNvPr', namespaces=namespaces).attrib['name']
            embed = pic.find('.//a:blip', namespaces=namespaces).attrib['{http://schemas.openxmlformats.org/officeDocument/2006/relationships}embed']
            embed_map[name] = embed
        root1 = ET.fromstring(relxml_content)
        namespaces1 = {'r': 'http://schemas.openxmlformats.org/package/2006/relationships'}
        for child in root1.findall('.//r:Relationship', namespaces=namespaces1):
            id_target_map[child.attrib['Id']] = child.attrib.get('Target')
        for name, embed in embed_map.items():
            if embed in id_target_map:
                target = id_target_map[embed]
                image_urls[name] = target
    return image_urls

def extract_image_from_excel(cell, image_urls, file_path):
    """从Excel单元格中提取图片并保存到images目录"""
    if cell is None:
        return None
    
    try:
        # 确保images目录存在
        images_dir = os.path.join(current_app.static_folder, 'images', 'logos')
        if not os.path.exists(images_dir):
            os.makedirs(images_dir)

        # 处理 DISPIMG 格式的图片
        if isinstance(cell.value, str) and cell.value.startswith('=_xlfn.DISPIMG('):
            # 提取图片ID
            img_id = cell.value.split('"')[1]
            image = image_urls[img_id]
            with zipfile.ZipFile(file_path, 'r') as zfile:
                with zfile.open("xl/"+image) as file:
                    image_data = BytesIO(file.read())
        else:
            # 处理普通嵌入式图片
            if not hasattr(cell, 'value'):
                return None
            if cell.value is None:
                return None
            
            image_data = BytesIO(cell.value)
        
        # 使用uuid生成唯一文件名
        image_path = os.path.join(images_dir, f"{uuid.uuid4()}.png")
        
        try:
            img = PILImage.open(image_data)
            # Convert palette images with transparency to RGBA
            if img.mode == 'P' and 'transparency' in img.info:
                img = img.convert('RGBA')
            
            # 处理透明图片
            if img.mode in ('RGBA', 'LA'):
                background = PILImage.new('RGB', img.size, (255, 255, 255))
                background.paste(img, mask=img.split()[-1])
                img = background
            # 处理其他颜色模式
            elif img.mode not in ('RGB', 'L'):
                img = img.convert('RGB')
            
            img.save(image_path, 'PNG')
            return image_path
        except Exception as e:
            print(f"图片处理错误: {str(e)}")
            return None
    except Exception as e:
        print(f"提取图片错误: {str(e)}")
        return None


@exhibition_bp.route('/export', methods=['GET'])
def export_exhibitions_txt():
    mode = request.args.get('mode')
    if mode == 'txt':
        """导出展会数据为txt文件"""
        try:
            # 获取所有展会数据
            exhibitions = g.exhibition_service.get_all_exhibitions()
            
            # 按行业分组
            industry_groups = {}
            for exhibition in exhibitions:
                industry = exhibition.get('industry', '其他')
                if industry not in industry_groups:
                    industry_groups[industry] = []
                industry_groups[industry].append(exhibition)
            
            # 创建临时文件
            report_path = os.path.join(current_app.static_folder, 'reports')
            os.makedirs(report_path, exist_ok=True)
            temp_file = os.path.join(report_path, f'exhibitions_{datetime.now().strftime("%Y%m%d%H%M%S")}.txt')
            
            # 写入数据
            with open(temp_file, 'w', encoding='utf-8') as f:
                for industry, exhibitions in industry_groups.items():
                    f.write(f"行业：{industry}\n")
                    f.write("=" * 50 + "\n\n")
                    
                    for exhibition in exhibitions:
                        f.write(f"展会编号：{exhibition.get('exhibition_number', '')}\n")
                        f.write(f"中文名称：{exhibition.get('chinese_name', '')}\n")
                        f.write(f"英文全称：{exhibition.get('english_name', '')}\n")
                        f.write(f"英文简称：{exhibition.get('english_short_name', '')}\n")
                        f.write(f"标识：{exhibition.get('logo', '')}\n")
                        f.write(f"举办时间：{exhibition.get('holding_time', '')}\n")
                        f.write(f"洲际：{exhibition.get('continent', '')}\n")
                        f.write(f"国别：{exhibition.get('country', '')}\n")
                        f.write(f"城市：{exhibition.get('city', '')}\n")
                        f.write(f"举办频率：{exhibition.get('frequency', '')}\n")
                        f.write(f"主办方：{exhibition.get('organizer', '')}\n")
                        f.write(f"主办方英文：{exhibition.get('organizer_en', '')}\n")
                        f.write(f"联系方式：{exhibition.get('contact_info', '')}\n")
                        f.write(f"展馆：{exhibition.get('exhibition_hall', '')}\n")
                        f.write(f"展馆英文：{exhibition.get('exhibition_hall_english', '')}\n")
                        f.write(f"官网：{exhibition.get('official_website', '')}\n")
                        f.write(f"展品范围：{exhibition.get('exhibit_scope', '')}\n")
                        f.write(f"简介：{process_description(exhibition.get('description', ''))}\n")
                        f.write(f"是否UFI认证：{'是' if exhibition.get('ufi_certified') == 1 else '否' if exhibition.get('ufi_certified') == 2 else ''}\n")
                        f.write(f"展会面积：{exhibition.get('exhibition_area', '')}㎡\n")
                        f.write(f"备注：{exhibition.get('remarks', '')}\n")
                        f.write("-" * 30 + "\n\n")
            
            
            # 返回下载链接
            return jsonify({
                'success': True,
                'message': '',
                'download_url': f'{Config.API_URL}/static/reports/{os.path.basename(temp_file)}'
            })
            
        except Exception as e:
            current_app.logger.error(f"导出txt文件失败: {str(e)}")
            return jsonify({
                'success': False,
                'message': '导出失败',
                'error': str(e)
            }), 500
            
    elif mode == 'excel':
        """导出展会数据为excel文件"""
        try:
            # 获取所有展会数据
            exhibitions = g.exhibition_service.get_all_exhibitions()
            
            # 创建工作簿
            wb = Workbook()
            ws = wb.active
            ws.title = "展会数据"
            
            # 定义表头
            headers = [
                '展览会编号', '行业', 'AI行业检查', '中文名称', '英文全称', '英文简称', '标识', '举办时间',
                '洲际', '国别', '城市', '举办频率', '主办方', '主办方英文',
                '联系方式', '展馆', '展馆英文', '官网', '展品范围', '简介',
                '是否UFI认证','是否供应链展会','是否CCPIT展会','备注'
            ]
            
            # 写入表头
            for col, header in enumerate(headers, 1):
                cell = ws.cell(row=1, column=col, value=header)
                cell.font = openpyxl.styles.Font(bold=True)
                ws.column_dimensions[chr(64 + col)].width = 20
            
            # 写入数据
            for row, exhibition in enumerate(exhibitions, 2):
                ws.cell(row=row, column=1, value=exhibition.get('exhibition_number'))
                ws.cell(row=row, column=2, value=exhibition.get('industry'))
                ws.cell(row=row, column=3, value=exhibition.get('industry_ai'))
                ws.cell(row=row, column=4, value=exhibition.get('chinese_name'))
                ws.cell(row=row, column=5, value=exhibition.get('english_name'))
                ws.cell(row=row, column=6, value=exhibition.get('english_short_name'))
                
                # 处理logo图片
                logo_url = exhibition.get('logo')
                if logo_url:
                    insert_image_to_cell(ws, row, 7, logo_url)
                
                ws.cell(row=row, column=8, value=exhibition.get('holding_time'))
                ws.cell(row=row, column=9, value=exhibition.get('continent'))
                ws.cell(row=row, column=10, value=exhibition.get('country'))
                ws.cell(row=row, column=11, value=exhibition.get('city'))
                ws.cell(row=row, column=12, value=exhibition.get('frequency'))
                ws.cell(row=row, column=13, value=exhibition.get('organizer'))
                ws.cell(row=row, column=14, value=exhibition.get('organizer_en'))
                ws.cell(row=row, column=15, value=exhibition.get('contact_info'))
                ws.cell(row=row, column=16, value=exhibition.get('exhibition_hall'))
                ws.cell(row=row, column=17, value=exhibition.get('exhibition_hall_english'))
                ws.cell(row=row, column=18, value=exhibition.get('official_website'))
                ws.cell(row=row, column=19, value=exhibition.get('exhibit_scope'))
                ws.cell(row=row, column=20, value=process_description(exhibition.get('description')))
                ws.cell(row=row, column=21, value='是' if exhibition.get('ufi_certified') == 1 else '否' if exhibition.get('ufi_certified') == 2 else '')
                ws.cell(row=row, column=22, value=exhibition.get('is_supply_chain'))
                ws.cell(row=row, column=23, value=exhibition.get('is_ccpit'))
                ws.cell(row=row, column=24, value=exhibition.get('remarks'))
            # 保存文件
            report_path = os.path.join(current_app.static_folder, 'reports')
            os.makedirs(report_path, exist_ok=True)
            report_file = os.path.join(report_path, f'exhibitions_{datetime.now().strftime("%Y%m%d%H%M%S")}.xlsx')
            wb.save(report_file)
            
            return jsonify({
                'success': True,
                'message': '',
                'download_url': f'{Config.API_URL}/static/reports/{os.path.basename(report_file)}'
            })
            
        except Exception as e:
            current_app.logger.error(f"导出Excel文件失败: {str(e)}")
            return jsonify({
                'success': False,
                'message': '导出失败',
                'error': str(e)
            }), 500
            
    elif mode == 'excel_industry':
        """按行业分类导出展会数据为excel文件"""
        try:
            # 获取所有展会数据
            exhibitions = g.exhibition_service.get_all_exhibitions()
            
            # 按行业分组并统计数量
            industry_groups = {}
            industry_counts = {}
            for exhibition in exhibitions:
                industry = exhibition.get('industry', '其他')
                if industry not in industry_groups:
                    industry_groups[industry] = []
                    industry_counts[industry] = 0
                industry_groups[industry].append(exhibition)
                industry_counts[industry] += 1
            
            # 按展会数量对行业进行降序排序
            sorted_industries = sorted(industry_counts.items(), key=lambda x: (-x[1], x[0]))
            
            # 创建工作簿
            wb = Workbook()
            
            # 删除默认sheet
            wb.remove(wb.active)
            
            # 定义表头
            headers = [
                '展览会编号', '行业','AI行业检查', '中文名称', '英文全称', '英文简称', '标识', '举办时间',
                '洲际', '国别', '城市', '举办频率', '主办方', '主办方英文',
                '联系方式', '展馆', '展馆英文', '官网', '展品范围', '简介',
                '是否UFI认证','是否供应链展会','是否CCPIT展会','备注'
            ]
            
            # 按照排序后的顺序创建sheet并写入数据
            for industry, count in sorted_industries:
                # 创建sheet名称：行业名(展会数量)，替换非法字符
                safe_industry_name = industry.replace('/', '_').replace('\\', '_').replace('?', '_').replace('*', '_').replace('[', '(').replace(']', ')')
                sheet_name = f"{safe_industry_name}({count}个)"
                
                # 如果sheet名称超过31个字符（Excel限制），进行截断
                if len(sheet_name) > 31:
                    sheet_name = f"{safe_industry_name[:25]}..({count})"
                
                ws = wb.create_sheet(title=sheet_name)
                
                # 写入表头
                for col, header in enumerate(headers, 1):
                    cell = ws.cell(row=1, column=col, value=header)
                    # 设置表头样式
                    cell.font = openpyxl.styles.Font(bold=True)
                    ws.column_dimensions[chr(64 + col)].width = 20
                
                # 写入数据
                for row, exhibition in enumerate(sorted(industry_groups[industry], key=lambda x: x.get('chinese_name', '')), 2):
                    ws.cell(row=row, column=1, value=exhibition.get('exhibition_number'))
                    ws.cell(row=row, column=2, value=exhibition.get('industry'))
                    ws.cell(row=row, column=3, value=exhibition.get('industry_ai'))
                    ws.cell(row=row, column=4, value=exhibition.get('chinese_name'))
                    ws.cell(row=row, column=5, value=exhibition.get('english_name'))
                    ws.cell(row=row, column=6, value=exhibition.get('english_short_name'))
                    
                    # 处理logo图片
                    logo_url = exhibition.get('logo')
                    if logo_url:
                        insert_image_to_cell(ws, row, 7, logo_url)
                    
                    ws.cell(row=row, column=8, value=exhibition.get('holding_time'))
                    ws.cell(row=row, column=9, value=exhibition.get('continent'))
                    ws.cell(row=row, column=10, value=exhibition.get('country'))
                    ws.cell(row=row, column=11, value=exhibition.get('city'))
                    ws.cell(row=row, column=12, value=exhibition.get('frequency'))
                    ws.cell(row=row, column=13, value=exhibition.get('organizer'))
                    ws.cell(row=row, column=14, value=exhibition.get('organizer_en'))
                    ws.cell(row=row, column=15, value=exhibition.get('contact_info'))
                    ws.cell(row=row, column=16, value=exhibition.get('exhibition_hall'))
                    ws.cell(row=row, column=17, value=exhibition.get('exhibition_hall_english'))
                    ws.cell(row=row, column=18, value=exhibition.get('official_website'))
                    ws.cell(row=row, column=19, value=exhibition.get('exhibit_scope'))
                    ws.cell(row=row, column=20, value=process_description(exhibition.get('description')))
                    ws.cell(row=row, column=21, value='是' if exhibition.get('ufi_certified') == 1 else '否' if exhibition.get('ufi_certified') == 2 else '')
                    ws.cell(row=row, column=22, value=exhibition.get('is_supply_chain'))
                    ws.cell(row=row, column=23, value=exhibition.get('is_ccpit'))
                    ws.cell(row=row, column=24, value=exhibition.get('remarks'))
            # 保存文件
            report_path = os.path.join(current_app.static_folder, 'reports')
            os.makedirs(report_path, exist_ok=True)
            report_file = os.path.join(report_path, f'exhibitions_by_industry_{datetime.now().strftime("%Y%m%d%H%M%S")}.xlsx')
            wb.save(report_file)
            
            return jsonify({
                'success': True,
                'message': '',
                'download_url': f'{Config.API_URL}/static/reports/{os.path.basename(report_file)}'
            })
            
        except Exception as e:
            current_app.logger.error(f"按行业导出Excel文件失败: {str(e)}")
            return jsonify({
                'success': False,
                'message': '导出失败',
                'error': str(e)
            }), 500

def get_ai_status_text(status):
    """获取AI检查状态的文字描述"""
    status_map = {
        0: '待检查',
        1: '已通过',
        2: '未通过',
        3: '已核对'
    }
    return status_map.get(status, '未知状态')

@exhibition_bp.route('/statistics', methods=['GET'])
def get_exhibition_statistics():
    """获取展会统计数据"""
    try:
        # 获取所有展会数据
        exhibitions = g.exhibition_service.get_all_exhibitions()
        
        # 获取所有行业类型
        industry_sql = """
        SELECT industry FROM industry_types 
        WHERE state = 0 
        ORDER BY industry
        """
        industry_data = db.execute_select(industry_sql)
        all_industries = [item['industry'] for item in industry_data]
        
        # 初始化统计数据
        statistics = {
            'totalExhibitions': len(exhibitions),
            'industry': {industry: 0 for industry in all_industries},  # 初始化所有行业计数为0
            'continent': {},
            'month': {},
            'country': {},
            'maxCity': '',
            'maxIndustry': '',
            'monthlyExhibitions': 0
        }
        
        # 城市统计
        city_count = {}
        # 国家统计
        country_count = {}
        # 洲际统计
        continent_count = {}
        # 月份统计
        month_count = {}
        
        # 当前月份
        current_month = datetime.now().month
        
        for exhibition in exhibitions:
            # 统计城市
            city = exhibition.get('city', '')
            city_count[city] = city_count.get(city, 0) + 1
            
            # 统计行业
            industry = exhibition.get('industry', '')
            if industry:  # 只统计有效的行业值
                statistics['industry'][industry] = statistics['industry'].get(industry, 0) + 1
            
            # 统计国家
            country = exhibition.get('country', '')
            country_count[country] = country_count.get(country, 0) + 1
            
            # 统计洲际
            continent = exhibition.get('continent', '')
            continent_count[continent] = continent_count.get(continent, 0) + 1
            
            # 统计月份
            event_month = exhibition.get('event_month')
            if event_month:
                month_count[event_month] = month_count.get(event_month, 0) + 1
                # 统计当月展会数量
                if int(event_month) == current_month:
                    statistics['monthlyExhibitions'] += 1
        
        # 获取展会数量最多的城市和行业
        if city_count:
            statistics['maxCity'] = max(city_count.items(), key=lambda x: x[1])[0]
        
        # 从已有展会中找出数量最多的行业
        exhibition_industries = {k: v for k, v in statistics['industry'].items() if v > 0}
        if exhibition_industries:
            statistics['maxIndustry'] = max(exhibition_industries.items(), key=lambda x: x[1])[0]
        
        # 整理各维度统计数据
        statistics['continent'] = continent_count
        statistics['month'] = {str(k): v for k, v in sorted(month_count.items(), key=lambda x: int(x[0]))}
        
        # 获取展会数量前5的国家
        top_countries = dict(sorted(country_count.items(), key=lambda x: x[1], reverse=True)[:5])
        statistics['country'] = top_countries
        
        return jsonify(statistics)
        
    except Exception as e:
        current_app.logger.error(f"获取统计数据失败: {str(e)}")
        return jsonify({'error': '获取统计数据失败'}), 500

# def insert_image_to_cell(ws, row, col, image_url, base_url="https://gem.ccpititc.com"):
#     """Helper function to insert and format image in Excel cell"""
#     try:
#         if not image_url or image_url == '无':
#             return
            
#         # 构建完整的图片URL
#         full_url = base_url + image_url if image_url.startswith('/') else image_url
        
#         try:
#             # 下载图片
#             response = requests.get(full_url, timeout=5)  # 添加超时设置
#             if response.status_code != 200:
#                 current_app.logger.warning(f"下载图片失败，URL: {full_url}, 状态码: {response.status_code}")
#                 return
#         except requests.exceptions.RequestException as e:
#             current_app.logger.warning(f"请求图片失败，URL: {full_url}, 错误: {str(e)}")
#             return
            
#         # 使用PIL处理图片大小
#         try:
#             img_data = BytesIO(response.content)
#             pil_image = PILImage.open(img_data)
#         except Exception as e:
#             current_app.logger.warning(f"打开图片失败，URL: {full_url}, 错误: {str(e)}")
#             return
        
#         # 计算新的图片尺寸，设置最大宽度和高度
#         max_width = 100  # Excel单元格中图片的最大宽度
#         max_height = 75  # Excel单元格中图片的最大高度
        
#         # 计算缩放比例
#         width_ratio = max_width / pil_image.width
#         height_ratio = max_height / pil_image.height
#         scale_ratio = min(width_ratio, height_ratio)
        
#         new_width = int(pil_image.width * scale_ratio)
#         new_height = int(pil_image.height * scale_ratio)
        
#         # 调整图片大小
#         pil_image = pil_image.resize((new_width, new_height), PILImage.Resampling.LANCZOS)
        
#         # 保存调整后的图片到BytesIO
#         output = BytesIO()
#         pil_image.save(output, format='PNG')
#         output.seek(0)
        
#         # 创建Excel图片对象
#         img = Image(output)
        
#         # 调整单元格大小以适应图片
#         ws.row_dimensions[row].height = max(ws.row_dimensions[row].height or 0, new_height * 0.75)  # 转换为Excel单位
        
#         # 将图片插入到单元格
#         ws.add_image(img, f"{chr(64 + col)}{row}")
        
#         # 设置单元格对齐方式
#         cell = ws.cell(row=row, column=col)
#         cell.alignment = Alignment(horizontal='center', vertical='center')
        
#     except Exception as e:
#         current_app.logger.error(f"插入图片失败: {str(e)}")
#         return

# 筛选分区 国家 地区 大洲
@exhibition_bp.route('/exhibition-items', methods=['GET'])
def exhibition_items():
    """获取展会筛选条件分类数据（大洲/国家/城市层级结构）"""
    try:
        # 获取所有展会数据
        search_data_sql = """
            SELECT DISTINCT continent, country, city 
            FROM exhibitions 
            WHERE state = 0 
            ORDER BY continent, country, city
        """
        data = db.execute_select(search_data_sql)
        
        # 构建层级结构
        result = {}
        
        for row in data:
            continent = row.get('continent', '')
            country = row.get('country', '')
            city = row.get('city', '')
            
            # 忽略空值
            if not continent or not country or not city:
                continue
                
            # 添加大洲
            if continent not in result:
                result[continent] = {}
                
            # 添加国家
            if country not in result[continent]:
                result[continent][country] = []
                
            # 添加城市（避免重复）
            if city not in result[continent][country]:
                result[continent][country].append(city)
        
        return jsonify(result)
    except Exception as e:
        current_app.logger.error(f"获取展会分类数据失败: {str(e)}")
        return jsonify({"error": f"获取数据失败: {str(e)}"}), 500

def insert_image_to_cell(ws, row, col, image_url):
    """Helper function to insert and format image in Excel cell"""
    try:
        if not image_url or image_url == '无':
            return
            
        # 构建本地图片路径
        if image_url.startswith('/static/'):
            image_url = image_url[7:]  # 移除开头的 /static/
        elif image_url.startswith('/app/static/'):
            image_url = image_url[11:]  # 移除开头的 /app/static/
        local_path = os.path.join(current_app.static_folder, image_url.lstrip('/'))
        
        try:
            # 检查文件是否存在
            if not os.path.exists(local_path):
                current_app.logger.warning(f"图片文件不存在: {local_path}")
                return
                
            # 使用PIL处理图片大小
            pil_image = PILImage.open(local_path)
            
            # 计算新的图片尺寸，设置最大宽度和高度
            max_width = 100  # Excel单元格中图片的最大宽度
            max_height = 75  # Excel单元格中图片的最大高度
            
            # 计算缩放比例
            width_ratio = max_width / pil_image.width
            height_ratio = max_height / pil_image.height
            scale_ratio = min(width_ratio, height_ratio)
            
            new_width = int(pil_image.width * scale_ratio)
            new_height = int(pil_image.height * scale_ratio)
            
            # 调整图片大小
            pil_image = pil_image.resize((new_width, new_height), PILImage.Resampling.LANCZOS)
            
            # 保存调整后的图片到BytesIO
            output = BytesIO()
            pil_image.save(output, format='PNG')
            output.seek(0)
            
            # 创建Excel图片对象
            img = Image(output)
            
            # 调整单元格大小以适应图片
            ws.row_dimensions[row].height = max(ws.row_dimensions[row].height or 0, new_height * 0.75)
            
            # 将图片插入到单元格
            ws.add_image(img, f"{chr(64 + col)}{row}")
            
            # 设置单元格对齐方式
            cell = ws.cell(row=row, column=col)
            cell.alignment = Alignment(horizontal='center', vertical='center')
            
        except Exception as e:
            current_app.logger.warning(f"处理图片失败: {local_path}, 错误: {str(e)}")
            return
            
    except Exception as e:
        current_app.logger.error(f"插入图片失败: {str(e)}")
        return

def process_description(text):
    """处理描述文本，确保以句号结尾"""
    if not text:
        return text
    text = str(text).strip()
    if not text.endswith('。'):
        text += '。'
    return text

@exhibition_bp.route('/ai_completion', methods=['POST'])
def trigger_ai_completion():
    """触发AI补全任务"""
    try:
        data = request.get_json()
        chinese_name = data.get('chinese_name')
        official_website = data.get('official_website')
        
        # 安全获取用户名
        username = 'unknown'
        if hasattr(g, 'user') and g.user:
            username = g.user.get('username', 'unknown')
        
        # 参数验证
        if not chinese_name or not official_website:
            return jsonify({
                'status': False,
                'error': '缺少必要参数：chinese_name 和 official_website 都是必需的'
            }), 400
            
        # 记录操作日志
        g.operation_service.create_log({
            'username': username,
            'operation_type': 'ai_completion',
            'status': 0,  # 任务开始
            'description': f'开始AI补全任务，展会名称：{chinese_name}'
        })
            
        # 准备AI补全任务的数据参数
        task_data = {
            'username': username,
            'chinese_name': chinese_name,
            'official_website': official_website
        }
            
        # 异步调用Celery任务，传递单个字典参数
        task = ai_completion_exhibition.delay(task_data)
        
        # 返回任务ID和监控指令
        return jsonify({
            'status': True,
            'task_id': task.id,
            'message': '任务已提交',
            'monitor_url': f'/exhibition/task_status?task_id={task.id}'
        })
        
    except Exception as e:
        current_app.logger.error(f"触发AI补全任务失败: {str(e)}")
        # 记录失败日志
        g.operation_service.create_log({
            'username': g.user.get('username', 'unknown'),
            'operation_type': 'ai_completion',
            'status': 2,  # 任务失败
            'description': f'AI补全任务失败：{str(e)}'
        })
        return jsonify({
            'status': False,
            'error': str(e)
        }), 500

@exhibition_bp.route('/task_status', methods=['GET'])
def get_task_status():
    """获取任务状态"""
    try:
        # 安全获取用户名
        username = 'unknown'
        if hasattr(g, 'user') and g.user:
            username = g.user.get('username', 'unknown')
            
        task_id = request.args.get('task_id')
        if not task_id:
            return jsonify({
                'status': False,
                'error': '缺少task_id参数'
            }), 400
            
        # 获取任务实例
        task_result = AsyncResult(task_id)
        
        response = {
            'task_id': task_id,
            'status': task_result.status,
        }
        
        # 如果任务完成，返回结果
        if task_result.ready():
            result = task_result.get()
            response['result'] = result
            
            # 记录完成日志
            g.operation_service.create_log({
                'username': username,
                'operation_type': 'ai_completion',
                'status': 1 if result.get('status', False) else 2,  # 1成功，2失败
                'description': f'AI补全任务完成：{result.get("message", "")}'
            })
            
        return jsonify(response)
        
    except Exception as e:
        current_app.logger.error(f"获取任务状态失败: {str(e)}")
        return jsonify({
            'status': False,
            'error': str(e)
        }), 500

@exhibition_bp.route('/region-map', methods=['GET'])
def get_region_map():
    """获取展会地区数据"""
    return jsonify(Base.REGION_MAP)

#  请求  行业
@exhibition_bp.route('/industry-map', methods=['GET'])
def get_industry_map():
    """获取行业类型数据"""
    try:
        # 查询所有行业类型
        sql = """
        SELECT industry FROM industry_types 
        WHERE state = 0 
        ORDER BY industry
        """
        data = db.execute_select(sql)
        
        # 转换数据格式为列表
        industry_types = [item['industry'] for item in data] if data else []
        
        return jsonify(industry_types)
    except Exception as e:
        current_app.logger.error(f"获取行业类型数据失败: {str(e)}")
        return jsonify({"error": f"获取数据失败: {str(e)}"}), 500

